package io.github.talelin.latticy.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import net.sf.jxls.transformer.XLSTransformer;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Map;

/**
 * @description
 * @author: XZX
 * @create: 2020/4/24 17:17
 **/
public class ExcelUtils {
    /**
     * 初使化Cell样式.
     *
     *@date 2015-5-14
     *@author YouMing.Z
     *@param workbook excell文档
     *@return 返回样式
     */
    public static XSSFCellStyle getCellStyle(XSSFWorkbook workbook)
    {
        // 设置字体
        XSSFFont cellfont = workbook.createFont();
        cellfont.setColor(HSSFFont.COLOR_RED);
        cellfont.setFontName("宋体");
        cellfont.setFontHeightInPoints((short) 9);// 字体大小

        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFont(cellfont);
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_LEFT);// 左右居中
        cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 上下居中
        //headstyle.setLocked(true);
        cellStyle.setWrapText(true);// 自动换行
        return cellStyle;
    }
    /**
     * 单元格信息转字符串
     *
     * @author zqk
     * @date 2016年11月1日
     * @param brandIdHSSFCell
     * @return
     * @throws Exception
     */
    public static String getStringCell(XSSFCell brandIdHSSFCell) throws Exception
    {
        String retVal = "";
        if (brandIdHSSFCell == null)
        {
            return "";
        }
        int type = brandIdHSSFCell.getCellType();
        if (type == HSSFCell.CELL_TYPE_NUMERIC)
        {
            if (HSSFDateUtil.isCellDateFormatted(brandIdHSSFCell))
            {// 处理日期格式、时间格式
                SimpleDateFormat sdf = null;
                if (brandIdHSSFCell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm"))
                {
                    sdf = new SimpleDateFormat("HH:mm");
                }
                else
                {// 日期
                    sdf = new SimpleDateFormat("yyyy-MM-dd");
                }
                Date date = brandIdHSSFCell.getDateCellValue();
                retVal = sdf.format(date);
            }
            else if (brandIdHSSFCell.getCellStyle().getDataFormat() == 58)
            {
                // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                double value = brandIdHSSFCell.getNumericCellValue();
                Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
                retVal = sdf.format(date);
            }
            else
            {
                Double cellDou = brandIdHSSFCell.getNumericCellValue();
                Object inputValue = null;// 单元格值
                Long longVal = Math.round(brandIdHSSFCell.getNumericCellValue());
                if(Double.parseDouble(longVal + ".0") == cellDou){   //判断是否含有小数位.0
                    inputValue = longVal;
                }
                else{
                    inputValue = cellDou;
                }
                BigDecimal df = new BigDecimal(inputValue+"");
                retVal = df.toPlainString();
            }
        }
        else if (type == HSSFCell.CELL_TYPE_BLANK)
        {
            retVal = "";
        }
        else if (type == HSSFCell.CELL_TYPE_STRING)
        {
            retVal = brandIdHSSFCell.getStringCellValue().trim();
        }
        if (retVal == null)
        {
            retVal = "";
        }
        return retVal;
    }
    /**
     * 添加错误提示.
     *
     *@date 2015-5-14
     *@author YouMing.Z
     *@param tips 错误提示
     *@param cellStyle 样式
     */
    public static void madeTipsToCell(XSSFRow hssfRow, int lastCellNum, String tips, XSSFCellStyle cellStyle)
    {
        XSSFCell cell = hssfRow.getCell(lastCellNum);
        if (cell == null)
        {
            cell = hssfRow.createCell(lastCellNum);
        }
        cell.setCellValue(tips);
        cell.setCellStyle(cellStyle);
    }

    /**
     * 导出成EXCEL1
     *
     * @author zqk
     * @date 2016年12月20日
     * @param beans
     * @param templateFileName 模板名称
     * @param excleName 导出文件名称
     * @param response
     */
    public static void outputToExcel(Map<?, ?> beans, String templateFileName, String excleName, HttpServletResponse response, HttpServletRequest request) {
        HSSFWorkbook book = null;
        XLSTransformer transformer = new XLSTransformer();
        OutputStream os = null;
        InputStream is = null;
        try {
            response.addHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(excleName, "UTF-8"));
            is = new BufferedInputStream(new FileInputStream(templateFileName));
            book = (HSSFWorkbook) transformer.transformXLS(is, beans);
            response.setContentType("application/vnd.ms-excel");
            os = response.getOutputStream();
            //将内容写入输出流并把缓存的内容全部发出去
            request.getSession().setAttribute("token", "N");
            book.write(os);
            os.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (is != null)
            {
                try
                {
                    is.close();
                }
                catch (IOException e)
                {
                }
            }
            if (os != null)
            {
                try
                {
                    os.close();
                }
                catch (IOException e)
                {
                }
            }
        }
    }

    public static void outputToExcel2(Map<?, ?> beans, String templateFileName, String excleName, HttpServletResponse response, HttpServletRequest request) {
        XSSFWorkbook book = null;
        XLSTransformer transformer = new XLSTransformer();
        OutputStream os = null;
        InputStream is = null;
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(excleName, "UTF-8"));
            is = new BufferedInputStream(new FileInputStream(templateFileName));
            book = (XSSFWorkbook) transformer.transformXLS(is, beans);
            response.setContentType("application/vnd.ms-excel");
            os = response.getOutputStream();
            //将内容写入输出流并把缓存的内容全部发出去
            request.getSession().setAttribute("token", "N");
            book.write(os);
            os.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (is != null)
            {
                try
                {
                    is.close();
                }
                catch (IOException e)
                {
                }
            }
            if (os != null)
            {
                try
                {
                    os.close();
                }
                catch (IOException e)
                {
                }
            }
        }
    }


}
